package com.lmonkey.servic;

import com.lmonkey.dao.Basedao;
import com.lmonkey.shop.CART;

import javax.servlet.jsp.jstl.sql.Result;
import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class CARTDao {
    public static ArrayList<CART> getcart(String uid){
        ArrayList<CART> list = new ArrayList<CART>();
        //声明结果集
        ResultSet rs = null;
       //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = "select * from lmonkey_cart where cart_u_id = ? and cart_valid = 1 order by cart_id desc ";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,uid);
            rs = ps.executeQuery();
            while(rs.next()){
                CART c = new CART(
                        rs.getInt("cart_id"),
                        rs.getString("cart_p_filename"),
                        rs.getString("cart_p_name"),
                        rs.getFloat("cart_p_price"),
                        rs.getInt("cart_quantity"),
                        rs.getInt("cart_p_stock"),
                        rs.getInt("cart_p_id"),
                        rs.getString("cart_u_id"),
                        rs.getInt("cart_valid")
                );
                list.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public  static CART getcartshop(String id){
        CART c = null;
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try{
            String sql = "select * from lmonkey_cart where cart_id = ? order by cart_id desc";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            rs = ps.executeQuery();
            while (rs.next()){
                c = new CART(
                        rs.getInt("cart_id"),
                        rs.getString("cart_p_filename"),
                        rs.getString("cart_p_name"),
                        rs.getFloat("cart_p_price"),
                        rs.getInt("cart_quantity"),
                        rs.getInt("cart_p_stock"),
                        rs.getInt("cart_p_id"),
                        rs.getString("cart_u_id"),
                        rs.getInt("cart_valid")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return c;
    }

    public  static CART getcartshop1(String id){
        CART c = null;
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try{
            String sql = "select * from lmonkey_cart where cart_id = ? order by cart_id desc";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            rs = ps.executeQuery();
            while (rs.next()){
                c = new CART(
                        rs.getInt("cart_id"),
                        rs.getString("cart_p_filename"),
                        rs.getString("cart_p_name"),
                        rs.getFloat("cart_p_price"),
                        rs.getInt("cart_quantity"),
                        rs.getInt("cart_p_stock"),
                        rs.getInt("cart_p_id"),
                        rs.getString("cart_u_id"),
                        rs.getInt("cart_valid")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return c;
    }

    public  static CART getcartshop(String uid,int pid){
        CART c = null;
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try{
            String sql = "select * from lmonkey_cart where cart_u_id = ? and cart_p_id = ? and cart_valid = 1 order by cart_id desc";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,uid);
            ps.setInt(2,pid);
            rs = ps.executeQuery();
            while (rs.next()){
                 c = new CART(
                        rs.getInt("cart_id"),
                        rs.getString("cart_p_filename"),
                        rs.getString("cart_p_name"),
                        rs.getFloat("cart_p_price"),
                        rs.getInt("cart_quantity"),
                        rs.getInt("cart_p_stock"),
                        rs.getInt("cart_p_id"),
                        rs.getString("cart_u_id"),
                        rs.getInt("cart_valid")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return c;
    }

    public static int updatenum(int esid , int count){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        int cp = 0;
        try {
            String sql = "update lmonkey_cart set cart_quantity = ? where cart_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,count);
            ps.setInt(2,esid);
            cp = ps.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }

        return cp;
    }
    public static int selectByid(String uid){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from lmonkey_cart where cart_u_id = ? and cart_valid = 1";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,uid);
            rs = ps.executeQuery();
            while (rs.next()){
                count = rs.getInt(1);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return  count;
    }

    public static ArrayList<CART> selectAll(int cp ,int count,String keyword){
        ArrayList<CART> list = new ArrayList<CART>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = null;
            if (keyword != null){
                sql = "select * from lmonkey_cart where cart_valid = 1 and cart_p_name like ? or cart_u_id like ? order by cart_id desc limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,"%"+keyword+"%");
                ps.setObject(2,"%"+keyword+"%");
                ps.setInt(3,count*(cp-1));
                ps.setInt(4,count);
            }else {
                sql = "select * from lmonkey_cart where cart_valid = 1 order by cart_id desc limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1,count*(cp-1));
                ps.setInt(2,count);
            }
            rs = ps.executeQuery();
            while(rs.next()){
                CART c = new CART(
                        rs.getInt("cart_id"),
                        rs.getString("cart_p_filename"),
                        rs.getString("cart_p_name"),
                        rs.getFloat("cart_p_price"),
                        rs.getInt("cart_quantity"),
                        rs.getInt("cart_p_stock"),
                        rs.getInt("cart_p_id"),
                        rs.getString("cart_u_id"),
                        rs.getInt("cart_valid")
                );
                list.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static int[] titlepage(int count,String keyword){
        int arr[]={0,1};
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        //设置结果集
        ResultSet rs = null;
        try {
            String sql =null;
            if (keyword == null) {
                sql = "select count(*) from lmonkey_cart where cart_valid = 1";
                ps = conn.prepareStatement(sql);
            }else {
                sql = "select count(*) from lmonkey_cart  where cart_valid = 1 and cart_p_name like ? or cart_u_id like ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,"%"+keyword+"%");
                ps.setObject(2,"%"+keyword+"%");
            }
                rs = ps.executeQuery();
                while (rs.next()){
                    arr[0] = rs.getInt(1);
                }
                if (arr[0]%count == 0){
                    arr[1] = arr[0]/count;
                }else {
                    arr[1] = (arr[0]/count)+1;
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arr;
    }

    public static int delete(int id){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = "delete from lmonkey_cart where cart_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static int update(int count){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        int cp = 0;
        try {
            String sql = "update lmonkey_cart set cart_valid = ? where cart_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,2);
            ps.setInt(2,count);
            cp = ps.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }

        return cp;
    }
}
